﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TodoApp.EntityFrameworkCore;
using TodoApp.ResultModel;
using Volo.Abp.DependencyInjection;
using Volo.Abp.EntityFrameworkCore;

namespace TodoApp.TMS_material.MaterialsProcurements
{
    public class TMS_ProcurementAppRepository : DapperDbContrxtRepository, ITransientDependency, ITMS_ProcurementAppRepository
    {
        public TMS_ProcurementAppRepository(IDbContextProvider<TodoAppDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }

        #region 物资采购添加
        public async Task<int> CreateProcurement(TMS_MaterialsProcuremenDto info)
        {

            var dbConnection = await GetDbConnectionAsync();
            info.CreateDate = DateTime.Now;
            info.Materials_Id = Guid.NewGuid();
            var list = await dbConnection.ExecuteAsync($"insert into TMS_MaterialsProcurement values ('{info.Materials_Id}' ,'{info.Materials_Name}','{info.Materials_Type}',{info.Materials_IPS},'{info.Materials_Specification}',{info.Materials_Country},{info.Materials_Province},{info.Materials_Num},'{info.Materials_Expectation}','{info.Materials_Use}','{info.Materials_Remark}','{info.CreateDate}','{info.PeopleId}','{info.PeopleIds}',1)", null,
                 await GetDbTransactionAsync());
            if (list > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }


        }
        #endregion

        #region 物资采购显示分页查询
        public async Task<PageResponse<TMS_MaterialsProcurementDto>> GetAllProcurement(string MaterialsName, int MaterialsIPS, string productionPlace_PName, string pName, int MaterialsState, int pageindex = 1, int pagesize = 5)
        {
            var result = new PageResponse<TMS_MaterialsProcurementDto>();
            var dbConnection = await GetDbConnectionAsync();
            var sql = "select a.Materials_Country,a.Materials_Province, a.Materials_Id,a.Materials_Name,a.Materials_Type,a.Materials_IPS,a.Materials_Specification,c.ProductionPlace_Name,d.ProductionPlace_Name as 'ProductionPlace_PName',a.Materials_Expectation,a.Materials_Num,a.Materials_Use,a.Materials_Remark,b.Employees_Name,a.CreateDate,a.Materials_State from TMS_MaterialsProcurement a join TMS_Employees b on a.PeopleId=b.Id join TMS_ProductionPlace c on a.Materials_Country=c.Id join TMS_ProductionPlace d on a.Materials_Province=d.Id where 1=1 ";
            if (!string.IsNullOrEmpty(MaterialsName))
            {
                sql += $" and a.Materials_Name='{MaterialsName}'";
            }
            if (MaterialsIPS>0)
            {
                sql += $" and a.Materials_IPS = {MaterialsIPS}";
            }
            if (!string.IsNullOrEmpty(productionPlace_PName))
            {
                sql += $" and d.ProductionPlace_Name = '{productionPlace_PName}'";
            }
            if (!string.IsNullOrEmpty(pName))
            {
                sql += $" and Employees_Name = '{pName}'";
            }
            if (MaterialsState > 0)
            {
                sql += $" and a.Materials_State = {MaterialsState}";
            }
            var list = await dbConnection.QueryAsync<TMS_MaterialsProcurementDto>(sql, transaction: await GetDbTransactionAsync());
            result.PageData = list.ToList();
            result.PageTotal = list.Count();
            sql += $" order by Materials_Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only ";
            var quer = await dbConnection.QueryAsync<TMS_MaterialsProcurementDto>(sql, transaction: await GetDbTransactionAsync());
            result.PageData = quer.ToList();
            return result;
        }
        #endregion

        #region 物资管理删除
        public async Task<int> DeleteContracts(string id)
        {

            var dbConnection = await GetDbConnectionAsync();
            return await dbConnection.ExecuteAsync($"delete from TMS_MaterialsProcurement where Materials_Id=@NewId", new { NewId = id },
                 await GetDbTransactionAsync());

            //var dbConnection = await GetDbConnectionAsync();
            //return await dbConnection.ExecuteAsync("update TMS_Contracts set Contract_Title=@NewName", new { NameName = name }, await GetDbTransactionAsync());
        }
        #endregion

        #region 物资管理批量删除
        public async Task<int> DeleteSContracts(string ids)
        {

            var dbConnection = await GetDbConnectionAsync();
            var list = 0;
            var obj = ids.Split(',');
            foreach (var item in obj)
            {
                list = await dbConnection.ExecuteAsync($"delete from TMS_MaterialsProcurement where Materials_Id = @Ids", new { Ids = item }, await GetDbTransactionAsync());
            }
            if (list > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }

            //var dbConnection = await GetDbConnectionAsync();
            //return await dbConnection.ExecuteAsync("update TMS_Contracts set Contract_Title=@NewName", new { NameName = name }, await GetDbTransactionAsync());
        }
        #endregion

        #region 物资管理修改
        public async Task<int> UpdateProcurement(TMS_MaterialsProcuremenDto info)
        {
            var dbConnection = await GetDbConnectionAsync();
            var list = await dbConnection.ExecuteAsync($"update TMS_MaterialsProcurement set Materials_Name = '{info.Materials_Name}',Materials_Type='{info.Materials_Type}',Materials_IPS={info.Materials_IPS},Materials_Specification='{info.Materials_Specification}',Materials_Country={info.Materials_Country},Materials_Province={info.Materials_Province},Materials_Num={info.Materials_Num},Materials_Expectation='{info.Materials_Expectation}',Materials_Use='{info.Materials_Use}',Materials_Remark='{info.Materials_Remark}' where Materials_Id='{info.Materials_Id}'", null,
                 await GetDbTransactionAsync());
            if (list > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        #endregion

    }
}
